package com.ys.zero.dao;

import java.util.List;
import java.util.Map;

import com.ys.zero.bean.DZtb;
import com.ys.zero.bean.Dict;
import com.ys.zero.bean.SCtb;
import com.ys.zero.bean.Uarticle;
import com.ys.zero.util.DBHelper;
import com.ys.zero.web.uArticalServlet;



public class articalDao {
		
		public List<Uarticle> showArtical(String uaid){
			String sql = "select * from uarticle where uaid=?";
			return DBHelper.query(sql, Uarticle.class,uaid);
		}
	
		/**
		 * 显示排行榜
		 * @return
		 */
		public List<Uarticle> showRank(){
			String sql = "select  uaid,uimage,utitle,praise from uarticle ORDER BY praise desc LIMIT 0,5";
			return DBHelper.query(sql, Uarticle.class);
		}
		
		/**
		 * 显示热门文章
		 * @return
		 */
		public List<Uarticle> showHot(){
			String sql = "select  u.uaid,create_time ,u.uimage,u.log,u.utitle,u.ucontent,u.visit from uarticle u where u.type=0 ORDER BY u.visit DESC LIMIT 0,5";
			return DBHelper.query(sql, Uarticle.class);
		}
		/**
		 * 显示标签
		 * @return
		 */
		public List<Uarticle> showLog(){
			String sql = "select b.log from uarticle a join dict b on a.log=b.log GROUP BY log ORDER BY SUM(visit) desc limit 0,5";
			return DBHelper.query(sql, Uarticle.class);
		}
		/**
		 * 显示头条（根据访问数查询）
		 * @return
		 */
		public List<Uarticle> showHeadNews(){
			String sql = "select  uaid,utitle from uarticle ORDER BY visit desc LIMIT 0,5";
			return DBHelper.query(sql, Uarticle.class);
		}
		/**
		 * 显示随便看看（随机查询）
		 */
		public List<Uarticle> showRandom(){
			String sql = "SELECT uaid,uimage,utitle,visit FROM uarticle\r\n" + 
					" WHERE uaid >= "
					+ " (SELECT FLOOR(RAND() * "
					+ " ((SELECT MAX(uaid) FROM uarticle)-"
					+ " (SELECT MIN(uaid) FROM uarticle)) + "
					+ " (SELECT MIN(uaid) FROM uarticle)))  \r\n" + 
					" ORDER BY uaid LIMIT 5;";
			return DBHelper.query(sql, Uarticle.class);
		}
		/**
		 * 显示创作排行
		 * @throws Exception 
		 */
		public List<Map<String, Object>>  showCreate() throws Exception{
			String sql  = "SELECT user.name,uid,count(*) as count FROM uarticle,user where uid = user.id GROUP BY uid ORDER BY count DESC ";
			return DBHelper.selectList(sql);	
}
		/*
		 * 上传问章的Html
		 */
		public int addTestHtml(Integer id,String type,String log,String utitle,String ucontent){
			String sql = "insert into uarticle(uid,create_time,type,log,utitle,ucontent) values(?,now(),?,?,?,?)";
			try {
				return DBHelper.update(sql, id,type,log,utitle,ucontent);
			} catch (Exception e) {
		
				e.printStackTrace();
			}
			return 0;
		}	
		public List<Dict> getType(){
			String sql = "select distinct  type from dict ";
			return DBHelper.query(sql, Dict.class);
		}
		public List<Dict> getLog(){
			String sql = "select distinct  log from dict ";
			return DBHelper.query(sql, Dict.class);
		}
		/**
		 * 据标签显示文章
		 * @param log
		 * @return
		 */
		public List<Uarticle> getUid(int uaid) {
			String sql = "select uid  from uarticle where uaid = ?";
			return DBHelper.query(sql,Uarticle.class,uaid);
		}
		/**
		 * 获取作者的id
		 * @param uaid
		 * @return
		 */
		public List<Uarticle> showContent(String log) {
			String sql = "select b.* from dict a join uarticle b on a.log=b.log where b.log = ? order by b.visit desc limit 0,4";
			return DBHelper.query(sql,Uarticle.class,log);
		}

		/**
		 * 搜索界面的大家搜索
		 * @return
		 */
		public List<Dict> showSeacherLog() {
			String sql = "";
			return DBHelper.query(sql,Dict.class);
		}
		/**
		 * 文章的总点赞数等
		 * @param id
		 * @return
		 * @throws Exception 
		 */
		public List<Map<String, Object>> getInfo(int id) throws Exception {
			String sql = "SELECT  SUM(praise),SUM(chat),SUM(visit),SUM(collection),count(uid) from uarticle where uid = ?";
			return DBHelper.selectList(sql, id);
				
		}
		
		
		/**
		 * 分类专栏
		 * @param log
		 * @return
		 */
		public List<Uarticle> showFLZL(String log) {
			String sql = "select log from uarticle where type in (select type from uarticle where log  = ? GROUP BY type) GROUP BY log";
			return DBHelper.query(sql,Uarticle.class,log);
		}
		/**
		 * 得到分类专栏的数量
		 * @param log
		 * @return
		 */
		public long getFlCount(String log) {
			String sql = "SELECT  count(*) from uarticle  where log =  ?";
			return (long) DBHelper.queryValue(sql, log);
		}
		/**
		 * 点赞
		 * @param uaid
		 */
		public void addPraise(String uaid) {
			String sql = "update uarticle set praise=praise+1 where uaid = ?";
			try {
				DBHelper.update(sql,uaid);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		/**
		 * 取消点赞
		 * @param uaid
		 */
		public void execPraise(String uaid) {
			String sql = "update uarticle set praise=praise-1 where uaid = ?";
			try {
				DBHelper.update(sql,uaid);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		/**
		 * 收藏
		 * @param uaid
		 */
		public void addCollection(String uaid) {
			String sql = "update uarticle set collection=collection+1 where uaid = ?";
			try {
				DBHelper.update(sql,uaid);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		/**
		 * 收藏
		 * @param uaid
		 */
		public void execCollection(String uaid) {
			String sql = "update uarticle set collection=collection-1 where uaid = ?";
			try {
				DBHelper.update(sql,uaid);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		/**
		 * 显示文章收藏
		 * @param account
		 * @return 
		 */
		public List<Map<String, Object>> showWZSC(String account) {
			try {
				String sql = "SELECT * from collection a join uarticle  b on  a.cuaid = b.uaid where a.cformat=0 and a.caccount = ?";
				return DBHelper.selectList(sql,account);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return null;
		}
		
		/**
		 * 记录点赞状态
		 * @param uaid
		 * @param id
		 * @return
		 * @throws Exception
		 */
		public int addDZstate(int uaid,int id) throws Exception{
			String sql = "insert into dztb(uaid,id,astate) values(?,?,2)";
			return DBHelper.update(sql, uaid,id);
		}
		public int dtDZstate(int uaid,int id) throws Exception{
			String sql = "delete  from dztb where uaid = ? and id = ?";
			return DBHelper.update(sql, uaid,id);
		}
		public List<DZtb> qrDZstate(int uaid,int id) throws Exception{
			String sql = "select  *  from dztb where uaid = ? and id = ?";
			return DBHelper.query(sql, DZtb.class, uaid,id);
		}
		
		/**
		 * 记录收藏状态
		 * @param uaid
		 * @param id
		 * @return
		 * @throws Exception
		 */
		public int addSCstate(int uaid,int id) throws Exception{
			String sql = "insert into sctb(uaid,id,astate) values(?,?,2)";
			return DBHelper.update(sql, uaid,id);
		}
		public int dtSCstate(int uaid,int id) throws Exception{
			String sql = "delete  from sctb where uaid = ? and id = ?";
			return DBHelper.update(sql, uaid,id);
		}
		public List<SCtb> qrSCstate(int uaid,int id) throws Exception{
			String sql = "select  *  from sctb where uaid = ? and id = ?";
			return DBHelper.query(sql, SCtb.class, uaid,id);
		}
		
		
		
		/**
		 * 更新visit
		 * @param uaid
		 */
		public void insertVisit(String uaid) {
			try {
				String sql = "update uarticle set visit = visit + 1 where uaid = ?";
				DBHelper.update(sql, uaid);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		
		
}
